import datetime
import json
from io import BytesIO

from flask import request, jsonify, Response
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from sqlalchemy import func, and_
from sqlalchemy.exc import IntegrityError

from tzq.document_management_system import dms_bp
from tzq.document_management_system.model.log_model import Log, Version
from tzq.document_management_system.model.message_model import Message
from tzq.document_management_system.model.permission_model import Permission
from tzq.extensions import db


# layui多条件分页查询接口
@dms_bp.route('/api/message', methods=['GET', 'POST'])
def get_message():
    page = int(request.args.get('page', 1))
    limit = int(request.args.get('limit', 10))

    # 获取搜索参数
    key = request.values.get("key")
    value = request.values.get("value")
    language = request.values.get("language")
    principal = request.values.get("principal")
    online_date = request.values.get("online_date")
    created_by = request.values.get("created_by")

    # 基础查询
    query = Message.query

    # 应用搜索条件
    query = query.filter(Message.delete_flag == 'N')
    if key:
        query = query.filter(Message.key.ilike(f'%{key}%'))
    if value:
        query = query.filter(Message.value.ilike(f'%{value}%'))
    if language:
        query = query.filter(Message.language.ilike(f'%{language}%'))
    if principal:
        query = query.filter(Message.principal.ilike(f'%{principal}%'))
    if online_date:
        # query = query.filter(Message.online_date.ilike(f'%{online_date}%'))
        # query = query.filter(Message.online_date == f'{online_date}')

        # 拆分字符串并转换为 date 对象列表
        try:
            date_list = [datetime.date.fromisoformat(d.strip()) for d in online_date.split(',')]
        except ValueError as e:
            print(f"日期格式错误: {e}")
            date_list = []
        # 查询 event_date 在这些日期中的所有记录
        query = query.filter(Message.online_date.in_(date_list))
    if created_by: query = query.filter(Message.created_by.ilike(f'%{created_by}%'))

    # 获取排序参数
    sort_field = request.args.get('field', 'message_id')
    sort_order = request.args.get('order', 'desc')

    # 构建排序
    if hasattr(Message, sort_field):
        if sort_order == 'asc':
            order_by = getattr(Message, sort_field).asc()
        else:
            order_by = getattr(Message, sort_field).desc()
        query = query.order_by(order_by)
    else:
        query = query.order_by(Message.online_date.desc(), Message.message_id.desc())

    # 分页查询
    pagination = query.paginate(page=page, per_page=limit, error_out=False)

    # 构建Layui要求的JSON格式
    return jsonify({
        'code': 0,
        'msg': '',
        'count': pagination.total,
        'data': [dataInfo.to_dict() for dataInfo in pagination.items]
    })


# 检查客户端用户，是否有新增、修改、删除权限
def check_ip_modify_permission(client_ip_i: str):
    # 判断是否有权限
    permission_temp = Permission.query.filter(
        and_(Permission.ip == client_ip_i, Permission.user_type.in_(['管理员', '读写用户']),
             Permission.delete_flag == 'N')).first()
    if not permission_temp:
        return False
    else:
        return True


# 检查用户IP是否为管理员
def is_administrator(client_ip_i: str):
    # 判断是否有权限
    permission_temp = Permission.query.filter(
        and_(Permission.ip == client_ip_i, Permission.user_type.in_(['管理员']),
             Permission.delete_flag == 'N')).first()
    if not permission_temp:
        return False
    else:
        return True


# layui的table的单元格编辑，保存到后台
@dms_bp.route('/api/messageUpdate', methods=['POST'])
def update_cell():
    try:
        # 判断是否有更新权限
        client_ip = request.remote_addr
        if not check_ip_modify_permission(client_ip):
            return jsonify({'status': 'error', 'message': f'你({client_ip})没有更新权限，请联系管理员twx1014920！'}), 200
        data = request.json
        instance_temp = Message.query.get(data['id'])
        if not instance_temp:
            return jsonify({'status': 'error', 'message': 'Message Info not found'}), 404
        # 判断该条信息是否是创建人，同时用户不能为管理员。
        if instance_temp.created_by != client_ip and not is_administrator(client_ip):
            return jsonify({'status': 'error', 'message': f'该记录不是你创建的，你无法修改！'}), 200

        v_old_value = ''
        v_new_value = data['value']
        # 更新对应字段
        if data['field'] == 'key':
            v_old_value = instance_temp.key
            instance_temp.key = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        elif data['field'] == 'value':
            v_old_value = instance_temp.value
            instance_temp.value = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        elif data['field'] == 'language':
            v_old_value = instance_temp.language
            instance_temp.language = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        elif data['field'] == 'principal':
            v_old_value = instance_temp.principal
            instance_temp.principal = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        elif data['field'] == 'online_date':
            v_old_value = instance_temp.online_date.strftime('%Y-%m-%d')
            instance_temp.online_date = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        elif data['field'] == 'comment':
            v_old_value = instance_temp.comment
            instance_temp.comment = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip

        # 记录日志
        log = Log(
            business_type='国际化',
            log_type='修改',
            ip=client_ip,
            field_name=data['field'],
            old_value=v_old_value,
            new_value=v_new_value,
        )
        db.session.add(log)
        db.session.commit()
        return jsonify({'status': 'success'})
    except Exception as e:
        db.session.rollback()
        return jsonify({'status': 'error', 'message': str(e)}), 500


# 添加数据接口
@dms_bp.route('/api/addMessage', methods=['POST'])
def add_compare_task():
    data = request.get_json()
    client_ip = request.remote_addr
    # 判断是否有权限
    if not check_ip_modify_permission(client_ip):
        return jsonify({'status': 'error', 'message': f'你({client_ip})没有添加权限，请联系管理员twx1014920！'}), 200

    try:
        message = Message(
            key=data['key'],
            value=data['value'],
            language=data['language'],
            principal=data['principal'],
            online_date=data['online_date_template'],
            comment=data['comment'],
            created_by=client_ip,
            last_updated_by=client_ip
        )
        db.session.add(message)
        db.session.commit()
        return jsonify({"code": 0, "msg": "添加成功"})
    except IntegrityError:
        return jsonify({"code": 1, "msg": "数据已存在"}), 400
    except Exception as e:
        return jsonify({"code": 2, "msg": str(e)}), 500


# 软删除数据接口
@dms_bp.route('/api/message/<int:id>', methods=['DELETE'])
def delete_compare_task(id):
    message = Message.query.get_or_404(id)
    try:
        # 判断是否有权限
        client_ip = request.remote_addr
        if not check_ip_modify_permission(client_ip):
            return jsonify({'status': 'error', 'message': f'你({client_ip})没有删除权限，请联系管理员twx1014920！'}), 200
        # 判断该条信息是否是创建人
        if message.created_by != client_ip and not is_administrator(client_ip):
            return jsonify({'status': 'error', 'message': f'该记录不是你创建的，你无法删除！'}), 200

        v_old_value = json.dumps(message.to_dict())

        message.delete_flag = 'Y'
        message.last_update_date = func.now()
        message.last_updated_by = client_ip

        # 记录日志
        log = Log(
            business_type='国际化',
            log_type='删除',
            ip=client_ip,
            old_value=v_old_value,
        )
        db.session.add(log)

        db.session.commit()
        return jsonify({"code": 0, "msg": "删除成功"})
    except Exception as e:
        return jsonify({"code": 1, "msg": str(e)}), 500


@dms_bp.route('/api/exportMessage', methods=['GET', 'POST'])
def export_message():
    """ 数据导出 """
    # 获取搜索参数 , POST方法 用 form.get
    key = request.values.get("key")
    value = request.values.get("value")
    language = request.values.get("language")
    principal = request.values.get("principal")
    online_date = request.values.get("online_date")
    created_by = request.values.get("created_by")

    # 基础查询
    query = Message.query

    # 应用搜索条件
    query = query.filter(Message.delete_flag == 'N')
    if key:
        query = query.filter(Message.key.ilike(f'%{key}%'))
    if value:
        query = query.filter(Message.value.ilike(f'%{value}%'))
    if language:
        query = query.filter(Message.language.ilike(f'%{language}%'))
    if principal:
        query = query.filter(Message.principal.ilike(f'%{principal}%'))
    if online_date:
        # 拆分字符串并转换为 date 对象列表
        try:
            date_list = [datetime.date.fromisoformat(d.strip()) for d in online_date.split(',')]
        except ValueError as e:
            print(f"日期格式错误: {e}")
            date_list = []
        # 查询 event_date 在这些日期中的所有记录
        query = query.filter(Message.online_date.in_(date_list))

    if created_by: query = query.filter(Message.created_by.ilike(f'%{created_by}%'))

    query = query.order_by(Message.online_date.desc(), Message.last_update_date.desc())

    # 分页查询
    query = query.paginate(page=1, per_page=1000000, error_out=False)

    # 定义Excel表头样式
    header_font = Font(name='Arial', bold=True, color="000000", size=12)
    header_fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    center_alignment = Alignment(horizontal="center", vertical="center")

    # 创建 Excel 工作簿和工作表
    wb = Workbook()
    ws = wb.active
    ws.title = "I18n"
    # 写入表头
    headers = [
        'Key',
        'IContent',
        'Language',
        'Remark',
        'Last Update Date',
        '责任人', '上线日期'
    ]
    ws.append(headers)
    # 为表头行（第1行）设置样式
    for col_num, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_num)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = center_alignment
    # 写入数据
    for item in query:
        ws.append([item.key,
                   item.value,
                   item.language,
                   '',
                   '',  # 最后更新时间可以不写，导入的时候就是当前时间了
                   item.principal,
                   item.online_date.strftime('%Y-%m-%d')
                   ])
    # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
    ws.freeze_panes = 'A2'
    # 启用自动筛选：从 A1 到最后一列最后一行
    last_col_letter = ws.cell(row=1, column=ws.max_column).column_letter
    last_row = ws.max_row
    ws.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
    # 调整列宽
    ws.column_dimensions['A'].width = 50  # Key
    ws.column_dimensions['B'].width = 50  # IContent
    ws.column_dimensions['C'].width = 15  # Language
    ws.column_dimensions['D'].width = 15  # Remark
    ws.column_dimensions['E'].width = 21  # Last Update Date
    ws.column_dimensions['F'].width = 15  # 责任人
    ws.column_dimensions['G'].width = 15  # 上线日期
    # 设置某几列单元格居中显示
    for col in ['C', 'G']:
        for row in ws.iter_rows(min_col=ws[col + '1'].column, max_col=ws[col + '1'].column):
            for cell in row:
                cell.alignment = Alignment(horizontal='center', vertical='center')

    date_string = datetime.datetime.now().strftime("%Y%m%d-%H%M%S")

    # 将工作簿写入 BytesIO 流
    output = BytesIO()
    wb.save(output)
    output.seek(0)

    if online_date:
        # 文件名不能有逗号
        online_date = online_date.replace(",", "_")
        version_string = "(v." + online_date + ")-"
    else:
        version_string = "ALL-"
    file_name = "jalor-I18n-" + version_string + date_string + ".xlsx"

    # 使用 Response 返回 Excel 文件
    return Response(
        output.getvalue(),
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        headers={
            "Content-Disposition": "attachment; filename=" + file_name
        }
    )


@dms_bp.route('/api/getMessageOnlineDate', methods=['GET'])
def get_message_online_date():
    """ 前端页面下拉框展示使用 """
    try:
        result = (db.session.query(
            Version.online_date,
            func.count().label('sl')
        ).outerjoin(
            Message,
            db.and_(
                Version.online_date == Message.online_date,
                Message.delete_flag == 'N'
            )
        )  # .filter(Version.enable_flag == 'Y')
                  .group_by(
            Version.online_date
        ).order_by(
            Version.online_date.desc()
        ).all())

        # tmp = []
        # for item in result:
        #     # item = item._asdict()
        #     item = {'online_date': item.strftime('%Y-%m-%d'),
        #             'sl': item.sl}
        #     tmp.append(item)

        # 提取结果（因为返回的是元组列表）, 提取元组中的第一个元素
        comp_task_names_data = [{"online_date": item[0].strftime('%Y-%m-%d'), "sl": item[1]} for item in result]
        return jsonify({"code": 0, "msg": "", "count": len(comp_task_names_data), "data": comp_task_names_data})
    except Exception as e:
        print("Database error:", e)
        return jsonify({"code": -1, "msg": str(e), "count": 0, "data": None})
